﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using RTSoft.Modules;
using System.Data.SqlClient;
using System.Data;

namespace RTSoft.SoureCode.Control.Luong
{
    class luongctrl
    {

        DataProvider provider = new DataProvider();
        SqlCommand cmd = new SqlCommand();
        SqlDataReader reader;
        //DataTable tbl;
        String strSQL = "";


        public int getSalary(ref String errConnect, ref String errSQL)
        {
            int result = 0;
            cmd.Connection = provider.Connection(ref errConnect);
            try
            {
                strSQL = "select top 1 LUONG_CB from LUONG order by ID_LUONG DESC";
                cmd.CommandText = strSQL;  
                reader = cmd.ExecuteReader();
                if (reader.Read())
                {
                    result = Convert.ToInt32(RTValidate.ZeroNull(reader[0]));
                }
                reader.Close();
            }
            catch (Exception ex)
            {
                errSQL = ex.Message;
                return 0;
            }
            finally
            {
                provider.Disconnect();
            }
            return result;
        }

        public String getDate(ref String errConnect, ref String errSQL)
        {
            String result = "";
            cmd.Connection = provider.Connection(ref errConnect);
            try
            {
                strSQL = "select top 1 NGAY from LUONG order by ID_LUONG DESC";
                cmd.CommandText = strSQL;
                reader = cmd.ExecuteReader();
                if (reader.Read())
                {
                    result = RTValidate.EmptyNull(reader[0]).ToString();
                }
                reader.Close();
            }
            catch (Exception ex)
            {
                errSQL = ex.Message;
                return "";
            }
            finally
            {
                provider.Disconnect();
            }
            return result;
        }
        public void getData(ref String errConnect, ref String errSQL, ref DataTable tbl)
        {
            cmd.Connection = provider.Connection(ref errConnect);
            try
            {
                tbl = new DataTable();
                strSQL = "GetSalary";
                cmd.CommandText = strSQL;
                cmd.CommandType = CommandType.StoredProcedure;                
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                DataSet ds = new DataSet();
                da.Fill(ds);
                tbl = ds.Tables[0];
            }
            catch (Exception ex)
            {
                errSQL = ex.Message;
            }
            finally
            {
                provider.Disconnect();
            }
        }

        public int getRowID(ref String errConnect, ref String errSQL, String code)
        {
            int result = 0;
            provider.Connection(ref errConnect);
            try
            {
                strSQL = "select ID_LUONG from LUONG Where LUONG_CB = @Code ";
                cmd.CommandText = strSQL;
                cmd.Parameters.AddWithValue("@Code", code);
                reader = cmd.ExecuteReader();
                if (reader.Read())
                {
                    result = Convert.ToInt32(RTValidate.ZeroNull(reader[0]));
                }
                reader.Close();
            }
            catch (Exception ex)
            {
                errSQL = ex.Message;
                return 0;
            }
            finally
            {
                provider.Disconnect();
            }
            return result;
        }

        public void Insert(ref String errConnect, ref String errSQL, luonginfo info)
        {
            cmd.Connection = provider.Connection(ref errConnect);
            if (errConnect != "")
            {
                return;
            }
            cmd.Transaction = provider.BeginTransaction();
            try
            {
                SqlParameter f = new SqlParameter();
                f.IsNullable = true;
                f.Value = System.DBNull.Value;
                strSQL = "Insert into LUONG";
                strSQL = strSQL + "(LUONG_CB,NGAY)";
                strSQL = strSQL + " values";
                strSQL = strSQL + "(@LUONG_CB,@NGAY)";
                cmd.CommandText = strSQL;
                cmd.Parameters.Clear();
                cmd.Parameters.AddWithValue("@LUONG_CB", info.LUONG_CB);
                cmd.Parameters.AddWithValue("@NGAY", info.NGAY);
                cmd.ExecuteNonQuery();
                provider.CommitTransaction();
            }
            catch (Exception ex)
            {
                errSQL = ex.Message;
                provider.RollbackTransaction();
                //' Neu xay ra loi thi rollback lai
            }
            finally
            {
                //'Dong ket noi
                cmd.Connection.Close();
                cmd.Connection.Dispose();
            }

        }

        public void Update(ref String errConnect, ref String errSQL, luonginfo info)
        {
            cmd.Connection = provider.Connection(ref errConnect);
            if (errConnect != "")
            {
                return;
            }
            cmd.Transaction = provider.BeginTransaction();
            try
            {
                SqlParameter f = new SqlParameter();
                f.IsNullable = true;
                f.Value = System.DBNull.Value;
                strSQL = "Update LUONG SET  ";
                strSQL = strSQL + "LUONG_CB=@LUONG_CB Where  ID_LUONG = @ID_LUONG";
                cmd.CommandText = strSQL;

                cmd.Parameters.AddWithValue("@ID_LUONG", info.ID_LUONG);
                cmd.Parameters.AddWithValue("@LUONG_CB", info.LUONG_CB);
                cmd.ExecuteNonQuery();
                provider.CommitTransaction();
            }
            catch (Exception ex)
            {
                errSQL = ex.Message;
                provider.RollbackTransaction();
                //' Neu xay ra loi thi rollback lai
            }
            finally
            {
                //'Dong ket noi
                cmd.Connection.Close();
                cmd.Connection.Dispose();
            }

        }

        public void Delete(ref String errConnect, ref String errSQL, int id)
        {
            cmd.Connection = provider.Connection(ref errConnect);
            if (errConnect != "")
            {
                return;
            }
            cmd.Transaction = provider.BeginTransaction();
            try
            {
                strSQL = "DELETE FROM LUONG WHERE ID_LUONG=@RowID";
                cmd.Parameters.AddWithValue("@RowID", id);
                cmd.CommandText = strSQL;
                cmd.ExecuteNonQuery();
                provider.CommitTransaction();
                //' Thuc hien transaction 
            }
            catch (Exception ex)
            {
                errSQL = ex.Message;
                provider.RollbackTransaction();
                //' Neu xay ra loi thi rollback lai
            }
            finally
            {
                //'Dong ket noi
                cmd.Connection.Close();
                cmd.Connection.Dispose();
            }
        }
    }
}
